
package example;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.awt.Toolkit;
import java.util.ArrayList;
import java.util.Timer;
import java.util.TimerTask;
import java.io.*;
import java.util.*;

import optimization.ConditionFieldIndex;
public class DBConnection {
//	public static String user ="kpham";
//	public static String passwd ="ao62dvvd";
//	public static String jdbcURL = "jdbc:oracle:thin:@oracle.cs.purdue.edu:1521:orb";
//	public static String driverName = "oracle.jdbc.driver.OracleDriver";

	
//	public static String jdbcURL = "jdbc:mysql://127.0.0.1:3306/ps";
//	public static String driverName = "com.mysql.jdbc.Driver";
	
	public static String user ="root";
	public static String passwd ="1";
	public static String jdbcURL = "jdbc:postgresql://localhost:5432/postgis";
	public static String driverName = "org.postgresql.Driver";
	
	public static int MYLOCK = -3;
	public static int OTHERLOCK = -1;
	public static int SHARELOCK = -2;
	public static int NOLOCK = -4;
	public static int SHARE = 4;
	public static int EXCLUSIVE = 6;
	public static int WAIT = 7;
	public static int NOWAIT = 8;
	Transaction[] tList = new Transaction[50];
	String content = "";
	public  Connection conn = null;
	public  Connection conn2 = null;
	public String message = "";
	public DBConnection()throws RemoteException , Exception {
		conn2 = null;
  		Class.forName(driverName).newInstance();
		  conn2 = DriverManager.getConnection(jdbcURL,user,passwd);
		  conn2.setAutoCommit(false);
	}
	public  void connect() throws RemoteException , Exception {
		 try {
		  Class.forName(driverName).newInstance();
		  conn = DriverManager.getConnection(jdbcURL,user,passwd);
		  conn.setAutoCommit(false);
		  
		 } catch(ClassNotFoundException cnfe) {
		      System.err.println(cnfe);
		      System.out.println("mysql Class not found");
		    } catch(SQLException sqle) {
		      System.err.println(sqle);
		      System.out.println("SQL exception");
		    }
		    catch (Exception e) {
	            e.printStackTrace();
	            System.out.println("Exception "+e.getMessage());
	        }
	}
	public int getCurrentTransactionNumber(String tableName) throws NumberFormatException, RemoteException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		int i = 0;
		String sql="Select currenttid from "+ tableName.substring(0,1) + "Current";
		i = Integer.parseInt(this.getSingleRow(sql));
		return i;
	}
	public void updateCurrentTransactionNumber(String tableName) throws RemoteException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		String sql = "update " +tableName.substring(0,1) + "Current SET currenttid = currenttid+1";
		executeCmd(sql);
		executeCmd("commit");
	}
	public  ArrayList<Tuples> executeQuery(String cmd) {
		ArrayList<Tuples> rst = new ArrayList<Tuples>();
		ResultSet rs =null;
  		Statement stmt = null;
  		String[] colList;
  		int rowCount = 0;
  		try {
  		cmd = cmd.trim();
  		stmt = conn2.createStatement();
  		  if (cmd.toUpperCase().startsWith("SELECT")) {
	  			int i,j = 0;
	  	  		i = cmd.toUpperCase().indexOf(" SELECT ");
	  	  		j = cmd.toUpperCase().indexOf(" FROM ");
	  	  		content = cmd.substring(i+8,j);
	  	  		content = content.trim();
	  	  		colList = content.split(",");
	  	  		for(i=0;i<colList.length;i++) {
	  	  			colList[i] = colList[i].trim();
	  	  		}
	  	  		String[] filedValue = new String[colList.length];
	  	  		content = "";    	  		
    			rowCount = 0;
    			cmd += " order by " + colList[0];
    			rs = stmt.executeQuery(cmd);  
    			
    			while(rs.next()) {
    				Tuples tuple = new Tuples();
    				for(i=0;i<colList.length;i++) {
    					content += rs.getString(colList[i]) + ";";
    					
    					tuple.tups.add(rs.getString(colList[i]));
    				}
    				content += ". ";
    				rowCount ++;
    				rst.add(tuple);
    			}
    			rs.close();
    			content = rowCount + "rows. " + content;			  
  		  }  		  
  		}
  		catch (SQLException es) {
  			System.out.println("sql exception "+es.getMessage());
  		}
  		catch (Exception e) {
  			System.out.println("exception "+e.getMessage());
  		}
  		System.out.println("content = "+content);
  		rs = null;  		
		return rst;
	}
	public  String executeCmd(String cmd) throws RemoteException , SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		ResultSet rs =null;
  		Statement stmt = null;
  		String content = "";
  		String[] colList;
		  //connect
		  
  		int rowCount = 0;
  		try {
  		cmd = cmd.trim();
  		stmt = conn2.createStatement();
  		  if (cmd.toUpperCase().startsWith("SELECT")) {
	  			int i,j = 0;
	  	  		i = cmd.toUpperCase().indexOf(" SELECT ");
	  	  		j = cmd.toUpperCase().indexOf(" FROM ");
	  	  		content = cmd.substring(i+8,j);
	  	  		content = content.trim();
	  	  		colList = content.split(",");
	  	  		for(i=0;i<colList.length;i++) {
	  	  			colList[i] = colList[i].trim();
	  	  		}
	  	  		content = "";
    	  		
    			rowCount = 0;
    			rs = stmt.executeQuery(cmd);  			
    			while(rs.next()) {
    				for(i=0;i<colList.length;i++)
    				content += rs.getString(colList[i]) + ";";
    				content += ". ";
    				rowCount ++;
    			}
    			rs.close();
    			content = rowCount + "rows. " + content;			  
  		  }
  		  else if (cmd.toUpperCase().startsWith("COMMIT")) { 
  			conn2.commit();
  			  content = "Data commited";
  		  }
  		  else if (cmd.toUpperCase().startsWith("ROLLBACK")) {
  			conn2.rollback();
  			  content = "Rollbacked";
  		  }
  		  else {  	
    			rowCount = stmt.executeUpdate(cmd);
    			content = rowCount + " updated";  
  		  }
  		}
  		catch (SQLException es) {
  			System.out.println("sql exception "+es.getMessage());
  		}
  		catch (Exception e) {
  			System.out.println("exception "+e.getMessage());
  		}
  		rs = null;
  		return content;
	}
	public  String getSingleRow(String cmd) throws RemoteException , SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		ResultSet rs =null;
  		Statement stmt = null;
  		String content = "";
  		try {
	  		stmt = conn2.createStatement();
	  		content = "";    	  		
			rs = stmt.executeQuery(cmd);  			
			while(rs.next()) {
				content += rs.getString(1) ;
			}
			rs.close();  		  
  		}
  		catch (SQLException es) {
  			System.out.println("sql exception "+es.getMessage());
  		}
  		catch (Exception e) {
  			System.out.println("exception "+e.getMessage());
  		}
  		rs = null;
  		return content;
	}
	public ArrayList<Tuples> joinResult(ArrayList<Tuples> localResult, ArrayList<Tuples> foreignRst, int type , ConditionFieldIndex cfi) {
		
		MergeJoin mj = new MergeJoin();	
		mj.merge(localResult, foreignRst, cfi);
		return mj.output;
	}
	
	public static void main(String args[]) throws RemoteException, Exception {
		ArrayList<Tuples> a = new ArrayList<Tuples>();
		ArrayList<Tuples> b = new ArrayList<Tuples>();
		DBConnection db = new DBConnection();
		db.connect();
		
		a = db.executeQuery("Select item from supplies ");;
		b = db.executeQuery("select  servingquantity from recipe ");
		
		MergeJoin mj = new MergeJoin();
		ConditionFieldIndex cfi = new ConditionFieldIndex(0,0,0);
		mj.merge(b, a,  cfi);
		
	}
	
}
